Re: [SQL] Using dates - Mailing list pgsql-sql

At 11:01 +0300 on 02/08/1999, maxsbox wrote:


> I am using pgsql 6.2.  Do later versions have this facility.
>
> I have tried Herouth's query from above and it works.  I tried d_start -
> 10000 but it resulted in another date. d_start - date(6-6-1966) or
> d_start - 6-6-1966 will not work.  I have looked throught the available
> functions in the user manual, but cannot find anything suitable.  That
> int4 is also precluded narrows the options somewhat.  Can you suggest a
> work around as the expected use for my data base will make heavy use of
> this. ie at least half the queries will bracket a span of dates.

Perhaps if you explain exactly what you want returned from your query I
will better understand your problem. What you have now describes shows a
problem in understanding the semantics of date arithmetic.

1) Comparing two dates: As far as I know, this worked ever since  6.1, which is the first version of Postgres I used.
Justmake  sure that both sides are indeed dates! It sounds to me as if  your trouble is that one of the side was
mistakenfor an  integer.
 
  ... WHERE d_start < '1999-04-14'::date
  should work well.

2) Subtracting integer from date. The semantics of this operation  is usually that the integer is number of days.
   testing=> select d_start, d_start - 30 from test2;      d_start|  ?column?   ----------+----------
01-15-1969|12-16-1968  07-14-1999|06-14-1999   04-13-1998|03-14-1998   12-01-1999|11-01-1999   (4 rows)
 
   The result is of type date even if your original d_start was   of type datetime and not date.

3) Subtracting a date from a date, as I said, gives you either a  timespan or an integer, depending on the type of the
operands. You must know how to format a correct date. Just writing  1999-4-7 without quotation marks, and preferably
adding ::date will probably not pass the parser, even. I'm not sure  the function date() worked in early versions.
 

4) Another option for date subtraction is the function age().
   testing=> select d_start, d_end, age( d_end, d_start ) from test2;      d_start|     d_end|age
----------+----------+----------------------------------  01-15-1969|08-01-1999|@ 30 years 6 mons 16 days 23 hours
07-14-1999|07-18-1999|@4 days   04-13-1998|03-12-1998|@ 1 mon 23 hours ago   12-01-1999|12-01-1999|@ 0   (4 rows)
 
   This function returns a timespan, for date or datetime operands.   The main difference between this and the timespan
returned  with simple datetime subtraction is that it converts to years,   months and days, instead of just days and
hours.

5) Finally, remember that you can compare timespans. Thus, you can  ask for all tuples two weeks back or later, for
example,using:
 
  testing=> SELECT * FROM test1 WHERE ( 'now' - t_end ) < '2 weeks';  t_start                     |t_end
----------------------------+---------------------------- Wed Jan 15 00:00:00 1969 IST|Sun Aug 01 00:00:00 1999 IDT
WedDec 01 00:00:00 1999 IST|Wed Dec 01 00:00:00 1999 IST  (2 rows)
 
  Or, in a better way for utilizing indices:
  testing=> SELECT * FROM test1  testing-> WHERE t_end > ( 'now'::datetime - '2 weeks'::timespan );  t_start
        |t_end  ----------------------------+----------------------------  Wed Jan 15 00:00:00 1969 IST|Sun Aug 01
00:00:001999 IDT  Wed Dec 01 00:00:00 1999 IST|Wed Dec 01 00:00:00 1999 IST  (2 rows)
 
  You noticed that Dec 01 1999 is in the future? Then makse sure  to add alse t_end < 'now'.

HTH,
Herouth

--
Herouth Maoz, Internet developer.
Open University of Israel - Telem project
http://telem.openu.ac.il/~herutma